package com.ai.imagetext.repository;

import com.ai.imagetext.entity.Image;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface ImageRepository extends JpaRepository<Image, Long> {
    
    /**
     * 查找未删除的图片
     */
    List<Image> findByIsDeletedFalseOrderByCreatedAtDesc();
    
    /**
     * 分页查找未删除的图片
     */
    Page<Image> findByIsDeletedFalse(Pageable pageable);
    
    /**
     * 根据ID查找未删除的图片
     */
    Optional<Image> findByIdAndIsDeletedFalse(Long id);
    
    /**
     * 根据文件名查找图片
     */
    Optional<Image> findByNameAndIsDeletedFalse(String name);
    
    /**
     * 根据标签搜索图片（模糊匹配）
     */
    @Query("SELECT DISTINCT i FROM Image i WHERE i.isDeleted = false AND i.tags LIKE %:tag%")
    List<Image> findByTagsContaining(@Param("tag") String tag);
    
    /**
     * 根据关键词搜索图片（名称、描述、标签）
     */
    @Query("SELECT DISTINCT i FROM Image i WHERE i.isDeleted = false AND " +
           "(i.name LIKE %:keyword% OR i.description LIKE %:keyword% OR i.tags LIKE %:keyword%)")
    Page<Image> findByKeyword(@Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 根据MIME类型查找图片
     */
    List<Image> findByMimeTypeAndIsDeletedFalse(String mimeType);
    
    /**
     * 根据AI识别状态查找图片
     */
    List<Image> findByAiRecognitionStatusAndIsDeletedFalse(Image.AIRecognitionStatus status);
    
    /**
     * 根据使用次数排序查找图片
     */
    Page<Image> findByIsDeletedFalseOrderByUsageCountDesc(Pageable pageable);
    
    /**
     * 根据创建时间范围查找图片
     */
    List<Image> findByIsDeletedFalseAndCreatedAtBetween(LocalDateTime startTime, LocalDateTime endTime);
    
    /**
     * 统计未删除的图片数量
     */
    long countByIsDeletedFalse();
    
    /**
     * 统计指定MIME类型的图片数量
     */
    long countByMimeTypeAndIsDeletedFalse(String mimeType);

    /**
     * 统计指定AI识别状态且未删除的图片数量
     */
    long countByAiRecognitionStatusAndIsDeletedFalse(Image.AIRecognitionStatus status);
    
    /**
     * 更新图片使用次数
     */
    @Modifying
    @Query("UPDATE Image i SET i.usageCount = i.usageCount + 1, i.updatedAt = CURRENT_TIMESTAMP WHERE i.id = :id")
    void incrementUsageCount(@Param("id") Long id);
    
    /**
     * 软删除图片
     */
    @Modifying
    @Query("UPDATE Image i SET i.isDeleted = true, i.updatedAt = CURRENT_TIMESTAMP WHERE i.id = :id")
    void softDeleteById(@Param("id") Long id);
    
    /**
     * 批量软删除图片
     */
    @Modifying
    @Query("UPDATE Image i SET i.isDeleted = true, i.updatedAt = CURRENT_TIMESTAMP WHERE i.id IN :ids")
    void softDeleteByIds(@Param("ids") List<Long> ids);
    
    /**
     * 获取热门标签（从图片的tags字段中提取）
     */
    @Query(value = "SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(i.tags, ',', numbers.n), ',', -1) as tag, " +
                   "COUNT(*) as count FROM images i " +
                   "JOIN (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) numbers " +
                   "ON CHAR_LENGTH(i.tags) - CHAR_LENGTH(REPLACE(i.tags, ',', '')) >= numbers.n - 1 " +
                   "WHERE i.is_deleted = false AND i.tags IS NOT NULL AND i.tags != '' " +
                   "GROUP BY tag HAVING tag != '' ORDER BY count DESC LIMIT :limit", 
           nativeQuery = true)
    List<Object[]> findPopularTags(@Param("limit") int limit);
    
    /**
     * 根据用户ID分页查找未删除的图片
     * @author wangguiyou
     */
    Page<Image> findByUserIdAndIsDeletedFalse(Long userId, Pageable pageable);
    
    /**
     * 统计指定用户的图片数量
     * @author wangguiyou
     */
    long countByUserIdAndIsDeletedFalse(Long userId);
}